CREATE OR REPLACE PACKAGE RecipeIDUtil IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================
--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION GenerateAbbrRecipeID (ContainerName IN VARCHAR2, SpecName IN VARCHAR2, RecipeRuleSetupName IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE GenerateAbbrRecipeID2 (ContainerName IN VARCHAR2, SpecName IN VARCHAR2, RecipeRuleSetupName IN VARCHAR2, AbbrRecipeID OUT VARCHAR2)
;

--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY RecipeIDUtil AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- GenerateAbbrRecipeID
--=====================
FUNCTION GenerateAbbrRecipeID (ContainerName IN VARCHAR2, SpecName IN VARCHAR2, RecipeRuleSetupName IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================


    --=====================
    -- Local variables
    --=====================
    sAbbrRecipeID VARCHAR2(40);

    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);

BEGIN

    --=====================
    -- Call the procedure
    --=====================
    GenerateAbbrRecipeID2 (ContainerName, SpecName, RecipeRuleSetupName, sAbbrRecipeID);

    --=====================
    -- Return success
    --=====================
    RETURN ('0 ' || sAbbrRecipeID);

EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('RecipeIDUtil.GenerateAbbrRecipeID->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('RecipeIDUtil.GenerateAbbrRecipeID->' || SQLERRM);
END GenerateAbbrRecipeID;

--=====================
-- GenerateAbbrRecipeID2
--=====================
PROCEDURE GenerateAbbrRecipeID2 (ContainerName IN VARCHAR2, SpecName IN VARCHAR2, RecipeRuleSetupName IN VARCHAR2, AbbrRecipeID OUT VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    recipeIDLength INTEGER :=3;
    currSequenceValue INTEGER := 0;
    sTempValue VARCHAR2(50) := '';
    sqlString VARCHAR2(500) := '';
    existCount INTEGER := 0;
    sequenceName VARCHAR2(30) := '';

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);

BEGIN

    sequenceName := 'RECIPESEQUENCE' || upper(SpecName) ; --Set the sequenceName upper
    SELECT COUNT(*) INTO existCount FROM user_sequences WHERE SEQUENCE_NAME = sequenceName; -- Check if the same name sequences is already exist
    IF existCount = 0 THEN
        sqlString := 'CREATE SEQUENCE ' || sequenceName || ' START WITH 1 MAXVALUE 9999999999 MINVALUE 1 CYCLE CACHE 10 NOORDER';
        EXECUTE IMMEDIATE  sqlString;
        COMMIT;
    END IF;

    sqlString := 'SELECT ' || sequenceName || '.NextVal FROM dual';
    EXECUTE IMMEDIATE  sqlString INTO currSequenceValue;

    IF length(currSequenceValue) < recipeIDLength THEN
        sTempValue := LPAD(currSequenceValue,recipeIDLength,'0');
    END IF;

    AbbrRecipeID := sTempValue;

EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'RecipeIDUtil.GenerateAbbrRecipeID2->' || SQLERRM);
END GenerateAbbrRecipeID2;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
